package cn.itlaobing.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.itlaobing.entity.Student;
import cn.itlaobing.vo.PageRequest;
import cn.itlaobing.vo.Pageable;

@Repository
public class StudentDaoImpl extends BaseDao implements StudentDao {

	@Override
	public void addStudent(Student student) {
		//用来获取自动增长值
		KeyHolder keyHolder = new GeneratedKeyHolder();  
	
		String sql="insert into students(name,gender,birthday) values(?,?,?)";
		Object[] params=new Object[]{student.getName(),student.getGender().name(),student.getBirthday()};
		
		super.getJdbcTemplate().update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement pstat=con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
				PreparedStatementSetter pss=new ArgumentPreparedStatementSetter(params);
				pss.setValues(pstat);
				return pstat;
			}
		},keyHolder);
		//获取返回的自增长id
		student.setId(keyHolder.getKey().longValue());
		
	}

	@Override
	public List<Student> findAll() {
		String sql="select id,name,gender,birthday from students";
		return super.getJdbcTemplate().query(sql, new BeanPropertyRowMapper<>(Student.class));
	}

	@Override
	public Student findById(Long id) {
		
		try {
			String sql="select * from students where id=?";
			return super.getJdbcTemplate().queryForObject(sql,new BeanPropertyRowMapper<>(Student.class),id);
		} catch (DataAccessException e) {
			return null;
		}
	}

	@Override
	public void update(Student student) {
		String sql="update students set name=?,gender=?,birthday=? where id=?";
		super.getJdbcTemplate().update(sql,student.getName(),student.getGender().name(),student.getBirthday(),student.getId());
	}

	@Override
	public void delete(Long id) {
		String sql="delete from students where id=?";
		super.getJdbcTemplate().update(sql,id);
		
	}
	
	
	private String buildSearchSql(String baseSql,Map<String,String> searchFilterMap){
		StringBuffer sb=new StringBuffer(baseSql);
		
		sb.append(" ").append("where 1=1");
		if(searchFilterMap.containsKey("name")){
			sb.append(" ")
			.append("and name like")
			.append(" ")
			.append("'%"+searchFilterMap.get("name")+"%'");
		}
		
		if(searchFilterMap.containsKey("gender")){
			sb.append(" ")
			.append("and gender='")
			.append(searchFilterMap.get("gender"))
			.append("'");
		}

		return sb.toString();
	}
	

	@Override
	public Pageable<Student> findPageable(PageRequest pageRequest) {
		
		String sql="select id,name,gender,birthday from students";
		//取出PageRequst中的 查询条件集合，附加到 上面的sql中
		sql=buildSearchSql(sql,pageRequest.getSearchFilter());
		
		log.info("查询SQL:"+sql);
		
		String paginationSql="select * from ("+sql+") as tmp limit ?,?";
		String countSql="select count(*) from ("+sql+") as tmp";
		
		
		//String sql="select id,name,gender,birthday from students";
		
		//String paginationSql="select id,name,gender,birthday from students  limit ?,?";
		//String countSql="select count(*) from  students";
		
		Long begin=(pageRequest.getPageNumber()-1)*pageRequest.getPageSize();
		Long size=pageRequest.getPageSize();
	
		
		List<Student> list=super.getJdbcTemplate().query(
				paginationSql, 
				new BeanPropertyRowMapper<>(Student.class), 
				begin,
				size);
		
		Long count=super.getJdbcTemplate().queryForObject(countSql,Long.class);
		
		Pageable<Student> studentPage=new Pageable<Student>();
		studentPage.setElements(list);
		studentPage.setPageNumber(pageRequest.getPageNumber());
		studentPage.setPageSize(pageRequest.getPageSize());
		studentPage.setTotal(count);
		//将pageRequest保存到 pageable中,因为pageable中需要查询条件来构建链接地址
		studentPage.setPageRequest(pageRequest);
		return studentPage;
	}


}
